{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49bd7f29-545f-4081-bcb4-ed630e1f5582",
   "metadata": {
    "vscode": {
     "languageId": "python"
    }
   },
   "outputs": [],
   "source": [
    "from codecs import utf_16_be_decode\n",
    "from re import U\n",
    "import pandas as pd\n",
    "import pymysql.cursors\n",
    "\n",
    "class MysqlUtils(object):\n",
    "    self.conn=pymysql.connect(\n",
    "        host='127.0.0.1',\n",
    "        user='root',\n",
    "        passwd='root',\n",
    "        db='tushare',\n",
    "        port='3307',\n",
    "        charset='utf8'\n",
    "    )\n",
    "\n",
    "    \n",
    "\n",
    "    class classIfication(object):\n",
    "        def __init__(self):\n",
    "            pass\n",
    "        def get__fina__indicator(self,conn):\n",
    "            \"\"\"\n",
    "            获取财务数据\n",
    "            \"\"\"\n",
    "\n",
    "            cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)\n",
    "            sql = \"\"\"\n",
    "            SELECT ts_code,ann_date,eps,total_revenue,undist_profit_ps,gross_margin,fcff,fcfe,tangible_asset,bps,grossprofit_margin,npta FROM financial_date WHERE ann_date <'2024-01-01' AND ann_date>='2023-01-01'\n",
    "            \"\"\"\n",
    "            cursor.execute(sql)\n",
    "            ret = cursor.fetchall()\n",
    "            df = pd.DataFrame(ret)\n",
    "            df1 = df.dropna(subset=['eps','total_revenue_ps','undist_profit_ps','gross_margin','fcff','fcfe','tangible_asset','bps','grossprofit_margin','npta'])\n",
    "            df1 = df1.reset_index(drop=False)\n",
    "            return df1\n",
    "        def get_daily(self,conn,df):\n",
    "            cursor=conn.cursor(cursor=pymysql.cursor.DictCursor)\n",
    "            new_list=[]\n",
    "\n",
    "        for i in range(len(df['ts_code'])):\n",
    "            ann_date_str=df['ann_date'].strftime('%Y%m%d')\n",
    "            sql='select trade_date,closes,from date_l where ts_code='\"+df['ts_code][i]+\"' and trade_date > Date('\"+ ann_date_str + \"') order vy trade_date asx limit 20'\n",
    "            cursor.execute(sql)\n",
    "\n",
    "            ret=cursor.fetvhall()\n",
    "            df1=pd.DataFrame(ret)\n",
    "\n",
    "            try:\n",
    "                if len(df1)>0:\n",
    "                    if len(df1) >0: \n",
    "                            max_close=df1['closes']. max() \n",
    "                            min_close = df1[' closes']. min() \n",
    "                            the_close= df1[' closes']. iloc[1] \n",
    "                            new_list.append({ \n",
    "                                'ts_code': df['ts_code'][i], \n",
    "                                'ann_date': df['ann_date'][i],\n",
    "                                'max_close': max_close, \n",
    "                                'min_close': min_close, \n",
    "                                'the_slose': the_close, \n",
    "                                'eps': df['eps'][i], \n",
    "                                'total_revenue_ps': df['total_revenue_ps'][i], \n",
    "                                'undist_profit_ps': df[' undist_profit_ps'][i], \n",
    "                                'gross_margin': df['gross_margin'][i], \n",
    "                                'fcff': df['fcff'][i], \n",
    "                                'fcfe': df['fcfe'][i], \n",
    "                                'tangible_asset': df['tangible_asset'][i],\n",
    "                                'bps': df['bps'][i], \n",
    "                                'grossprofit margin': df['grossprofit_margin'][i], \n",
    "                                'npta': df['npta'][i]\n",
    "                        })\n",
    "            except Exception as e: \n",
    "                print(e) \n",
    "if __name__=='__main__':\n",
    "    mu=MysqlUtils()\n",
    "    ci=classIfication()\n",
    "    df=ci.get_fina_indicator(mu.conn)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "",
   "name": ""
  },
  "language_info": {
   "name": ""
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
